/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package aps_db;

import DB_Manage.BillingSiteData;
import DB_Manage.DatabaseManager;
import DB_Manage.User;
import Integrity.CreditStatement;
import Integrity.MunicipalityStatement;
import Integrity.TelcoStatement;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;
import scheduler.ScheduledItem;

/**
 *
 * @author user
 */
public class DB_Admin {

    private static Connection _Connection;
    private static String _FileName = "database2.properties";
    DatabaseManager dbm = new DatabaseManager();

    public DB_Admin() throws IOException, ClassNotFoundException, SQLException
    {
        this.init();
    }

    public void testDB() throws SQLException
    {
        dropTables();
        System.out.println("Database cleared");

        createTables();
        System.out.println("New tables created");

        populateTables();
        System.out.println("Initial tables populated");

        System.out.println("\n----- Testing Public Methods -----");
        retrUserTest();
        retrPasswordTest();
        retrBillingSiteTest();
        getBillingRefListTest();
        getBillingSiteTypeTest();
        retrScheduleItemsTest();
        retrUserDataResultTest();
        registerUserTest();
        updateUserTest();
    }

    private void registerUserTest() throws SQLException
    {
        String uid = dbm.registerUser("secret", "Joe", "Tester", "joe@email.com",
            "ID--8765", "011 555 4357", "06/05/1966");
        if("UID4".compareTo(uid) == 0)
        {
            System.out.println("PASSED :registerUser()");
        }
        else
        {
            System.out.println("\tFAILED : registerUser()");
        }
    }

    private void updateUserTest() throws SQLException
    {
        User usr = dbm.retrUser("UID001");
        usr.setFirstName("Johann");
        usr.getJobs().get(0).getAuthenticationPairs()[0].setValue("secret");
        dbm.updateUser(usr);

        Statement stat = _Connection.createStatement();
        ResultSet result = stat.executeQuery("SELECT FirstName FROM Users WHERE UserID = 'UID001'");
        result.next();

        Statement stat2 = _Connection.createStatement();
        ResultSet result2 = stat2.executeQuery("SELECT AuthVal0 FROM Jobs WHERE UserID = 'UID001' AND BillingSiteRef = 'BR001'");
        result2.next();

        if(("Johann".compareTo(result.getString("FirstName")) == 0)
                && ("secret".compareTo(result2.getString("AuthVal0")) == 0))
        {
            System.out.println("PASSED : updateUser()");
        }
        else
        {
            System.out.println("FAILED : updateUser()");
        }


    }

    private void retrUserDataResultTest() throws SQLException
    {
        System.out.println("---- Data retrieve tests");

        TelcoStatement telState = (TelcoStatement) dbm.retrUserDataResult("UID003", "BR001");
        MunicipalityStatement munState = (MunicipalityStatement) dbm.retrUserDataResult("UID003", "BR002");
        CreditStatement credState = (CreditStatement) dbm.retrUserDataResult("UID003", "BR003");

        // common
        if((commonDateCheck((Integrity.Statement) telState))
                && (commonDateCheck((Integrity.Statement) munState))
                && (commonDateCheck((Integrity.Statement) credState)))
        {
            System.out.println("\tPASSED :common retrieve");
        }
        else
        {
            System.out.println("\tFAILED : common retrieve");
        }

        // telco
        if(("011 456 8870".compareTo(telState.get_tel_no()) == 0)
                && ("13:45:56".compareTo(telState.get_t_call_dur()) == 0)
                && (55.55 == telState.get_serv_chrg())
                && (400.55 == telState.get_call_chrg())
                && (88 == telState.get_t_no_call()))
        {
            System.out.println("\tPASSED : telco retrieve");
        }
        else
        {
            System.out.println("\tFAILED : telco retrieve");
        }

        // municipality
        if(("700KWh".compareTo(munState.get_elec_used()) == 0)
                && (4002.88 == munState.get_elec_chrg())
                && ("3456L".compareTo(munState.get_water_used()) == 0)
                && (30982.77 == munState.get_water_chrg())
                && (5523.00 == munState.get_sew_chrg())
                && (8832.77 == munState.get_ref_chrg()))
        {
            System.out.println("\tPASSED : municipality retrieve");
        }
        else
        {
            System.out.println("\tFAILED : municipality retrieve");
        }

        // credit
        if(("Visa".compareTo(credState.get_card_type()) == 0)
                && (22.70 == credState.get_int_rate())
                && (50000.00 == credState.get_credit_lim())
                && (18000.70 == credState.get_credit_avl())
                && (2000.00 == credState.get_min_amount_due()))
        {
            System.out.println("\tPASSED : Credit retrieve");
        }
        else
        {
            System.out.println("\tFAILED : credit retrieve");
        }
    }

    private boolean commonDateCheck(Integrity.Statement state)
    {
        java.sql.Date test = new java.sql.Date(state.get_date().getTime());
        String sTest = test.toString();
        //System.out.println(sTest);

        if(("acc190823".compareTo(state.get_acc_no()) == 0)
                && ("Fred Frank".compareTo(state.get_acc_holder()) == 0)
                && ("2010-05-30".compareTo(sTest) == 0)
                && ("no100922".compareTo(state.get_statm_no()) == 0)
                && ("May".compareTo(state.get_statm_month()) == 0)
                && (2000.67 == state.get_t_due())
                && ("2010-08-30".compareTo(state.get_due_date()) == 0)
                && (10000.67 == state.get_op_bal())
                && (2000.67 == state.get_cl_bal())
                && (8000.00 == state.get_pay_rec())
                && (1000.00 == state.get_new_chrg())
                && (1000.00 == state.get_ded())
                && (2000.00 == state.get_disc())
                && (250.00 == state.get_vat_amount()))
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    private void retrScheduleItemsTest() throws SQLException
    {
        ArrayList<ScheduledItem> schedule = dbm.retrScheduleItems();
        if(6 == schedule.size())
        {
            System.out.println("PASSED : retrScheduleItems()");
        }
        else
        {
            System.out.println("FAILED : retrScheduleItems()");
        }
    }

    private void getBillingSiteTypeTest() throws SQLException
    {
        if(("telco".compareTo(dbm.getBillingSiteType("BR001")) == 0)
                && ("credit".compareTo(dbm.getBillingSiteType("BR003")) == 0)
                && ("municipality".compareTo(dbm.getBillingSiteType("BR002")) == 0))
        {
            System.out.println("PASSED : getBillingSiteType()");
        }
        else
        {
            System.out.println("FAILED : getBillingSiteType()");
        }
    }

    private void getBillingRefListTest() throws SQLException
    {
        ArrayList<String> list = dbm.getBillingRefList();

        if((3 == list.size()) && ("BR001".compareTo(list.get(0)) == 0)
                && ("BR002".compareTo(list.get(1)) == 0)
                && ("BR003".compareTo(list.get(2)) == 0))
        {
            System.out.println("PASSED : getBillingRefList()");
        }
        else
        {
            System.out.println("FAILED : getBillingRefList()");
        }
    }

    private void retrBillingSiteTest() throws SQLException
    {
        BillingSiteData site = dbm.retrBillingSite("BR003");

        if(("Edgars".compareTo(site.getBillSiteName()) == 0)
                &&("credit".compareTo(site.getType()) == 0)
                &&("script3.xml".compareTo(site.getScript()) == 0)
                &&("3 day".compareTo(site.getMaintanenceWindow()) == 0)
                &&(3 == site.getRetryInterval())
                &&(30 == site.getBillingCycle())
                &&(5 == site.getLeadTime())
                &&(6 == site.getConcurrent())
                &&("User Name".compareTo(site.getIDFields()[0]) == 0)
                &&("Card Number".compareTo(site.getIDFields()[1]) == 0)
                &&("Password".compareTo(site.getAuthFields()[0]) == 0)
                &&("PIN".compareTo(site.getAuthFields()[1]) == 0))
        {
            System.out.println("PASSED : retrBillingSite()");
        }
        else
        {
            System.out.println("FAILED : retrBillingSite()");
        }
    }

    private void retrPasswordTest() throws SQLException
    {
        if("hat".compareTo(dbm.retrPassword("UID001")) == 0)
        {
            System.out.println("PASSED : retrPassword()");
        }
        else
        {
            System.out.println("FAILED : retrPassword()");
        }
    }

    private void retrUserTest() throws SQLException
    {
        User usr = dbm.retrUser("UID001");

        if(("Peter".compareTo(usr.getFirstName()) == 0)
                &&("Parker".compareTo(usr.getSurname()) == 0)
                &&("parker@email.com".compareTo(usr.getEmailAddress()) == 0)
                &&("70000".compareTo(usr.getIDNumber()) == 0)
                &&("011 555 1234".compareTo(usr.getPhoneNumber()) == 0)
                &&("12/31/1950".compareTo(usr.getDateOfBirth()) == 0))
        {
            System.out.println("PASSED : retrUser()");
        }
        else
        {
            System.out.println("FAILED : retrUser()");
        }
    }

    private void init()throws IOException, ClassNotFoundException, SQLException
    {
        Properties props = new Properties();
        FileInputStream in = new FileInputStream(_FileName);
        props.load(in);

        String driver = props.getProperty("jdbc.driver");
        String url = props.getProperty("jdbc.url");
        String username = props.getProperty("jdbc.username");
        String password = props.getProperty("jdbc.password");
        Class.forName(driver);

        _Connection = DriverManager.getConnection(url,username,password);
    }

    public void createTables() throws SQLException
    {
        Statement stat = _Connection.createStatement();

        stat.execute("CREATE TABLE Test (Name VARCHAR(20))");

        stat.execute("CREATE TABLE Users (UserID VARCHAR(50) PRIMARY KEY, Password VARCHAR(50), " +
                "FirstName VARCHAR(100), Surname VARCHAR(100), Email VARCHAR(100)," +
                " IDNumber VARCHAR(30), PhoneNumber VARCHAR(20), DOB VARCHAR(50))");

        stat.execute("CREATE TABLE BillingSiteTypes (Type VARCHAR(50))");
        stat.execute("CREATE TABLE ErrorCodes (ErrorCode VARCHAR(50), Description VARCHAR(200))");

        stat.execute("CREATE TABLE BillingSites (BillingSiteRef VARCHAR(50) PRIMARY KEY, BillingSiteName VARCHAR(100)," +
                " Type VARCHAR(50), Script VARCHAR(100), MaintenanceWindow VARCHAR(100), RetryInterval INTEGER," +
                " BillingCycle INTEGER, LeadTime INTEGER, Concurrent INTEGER," +
                " Ident0 VARCHAR(100),Ident1 VARCHAR(100),Ident2 VARCHAR(100),Ident3 VARCHAR(100),Ident4 VARCHAR(100)," +
                " Auth0 VARCHAR(100), Auth1 VARCHAR(100), Auth2 VARCHAR(100), Auth3 VARCHAR(100), Auth4 VARCHAR(100))");

        stat.execute("CREATE TABLE Jobs (UserID VARCHAR(50), BillingSiteRef VARCHAR(50)," +
                " LastRun DATE, LastStatementDate DATE, LastStatementTotal DECIMAL(10,2), LatestResult VARCHAR(50)," +
                " Status VARCHAR(10)," +
                " IdentVal0 VARCHAR(100),IdentVal1 VARCHAR(100),IdentVal2 VARCHAR(100),IdentVal3 VARCHAR(100),IdentVal4 VARCHAR(100)," +
                " AuthVal0 VARCHAR(100), AuthVal1 VARCHAR(100), AuthVal2 VARCHAR(100), AuthVal3 VARCHAR(100), AuthVal4 VARCHAR(100)," +
                " PRIMARY KEY (UserID, BillingSiteRef))");

        stat.execute("CREATE TABLE Common (UserID VARCHAR(50), BillingSiteRef VARCHAR(50)," +
                " AccNum VARCHAR(100), AccHolder VARCHAR(100), StateDate DATE, StateNum VARCHAR(100)," +
                " StateMonth VARCHAR(100), TotalDue DECIMAL(10,2), DueDate DATE, " +
                " OpenBal DECIMAL(10,2), CloseBal DECIMAL(10,2), PayReceived DECIMAL(10,2)," +
                " NewCharges DECIMAL(10,2), Deducts DECIMAL(10,2), Discount DECIMAL(10,2), Vat DECIMAL(10,2)," +
                " PRIMARY KEY (UserID, BillingSiteRef))");

        stat.execute("CREATE TABLE CreditCards (UserID VARCHAR(50), BillingSiteRef VARCHAR(50)," +
                " CardType VARCHAR(100), IntRate DECIMAL(10,2)," +
                " CredLimit DECIMAL(10,2), CredAvail DECIMAL(10,2), MinDue DECIMAL(10,2)," +
                " PRIMARY KEY (UserID, BillingSiteRef))");

        stat.execute("CREATE TABLE Telco (UserID VARCHAR(50), BillingSiteRef VARCHAR(50)," +
                " TelNum VARCHAR(100), TotalDuration VARCHAR(100)," +
                " ServiceChrg DECIMAL(10,2), CallChrg DECIMAL(10,2), TotalCalls INTEGER," +
                " PRIMARY KEY (UserID, BillingSiteRef))");

        stat.execute("CREATE TABLE Municipality (UserID VARCHAR(50), BillingSiteRef VARCHAR(50)," +
                " ElecUsed VARCHAR(100), ElecCharge DECIMAL(10,2)," +
                " WaterUsed VARCHAR(100), WaterCharge DECIMAL(10,2)," +
                " SewageCharge DECIMAL(10,2), RefuseCharge DECIMAL(10,2)," +
                " PRIMARY KEY (UserID, BillingSiteRef))");

    }

    public void populateTables() throws SQLException
    {
        Statement stat = _Connection.createStatement();

        stat.execute("INSERT INTO Test VALUES ('Romeo')");

        //Users
        stat.execute("INSERT INTO Users VALUES ('UID001','hat','Peter', 'Parker'," +
                        "'parker@email.com','70000','011 555 1234', '12/31/1950')");
        stat.execute("INSERT INTO Users VALUES ('UID002','hat','David', 'Donald'," +
                "'donald@email.com','80000','011 555 5678', '2/13/1960')");
        stat.execute("INSERT INTO Users VALUES ('UID003','hat','Fred', 'Frank'," +
                "'frank@email.com','90000','011 555 9012', '4/27/1970')");

        //Types
        stat.execute("INSERT INTO BillingSiteTypes VALUES ('telco')");
        stat.execute("INSERT INTO BillingSiteTypes VALUES ('municipality')");
        stat.execute("INSERT INTO BillingSiteTypes VALUES ('credit')");

        //BillingSites
        stat.execute("INSERT INTO BillingSites VALUES " +
                "('BR001', 'Vodacom','telco','script.xml','one day',3,30,5,6," +
                "'User Name','','','',''," +
                "'Password','','','','')");
        stat.execute("INSERT INTO BillingSites VALUES " +
                "('BR002', 'Johannesburg','municipality','script2.xml','2 day',7,60,6,5," +
                "'User Name','Address','','',''," +
                "'Password','','','','')");
        stat.execute("INSERT INTO BillingSites VALUES " +
                "('BR003', 'Edgars','credit','script3.xml','3 day',3,30,5,6," +
                "'User Name','Card Number','','',''," +
                "'Password','PIN','','','')");


        //Jobs
        stat.execute("INSERT INTO Jobs VALUES ('UID001','BR001','06/09/2010','05/30/2010'," +
                "2000.67,'success','active'," +
                "'usr','','','',''," +
                "'pw','','','','')");

        stat.execute("INSERT INTO Jobs VALUES ('UID002','BR001','06/09/2010','05/30/2010'," +
                "2000.67,'success','active'," +
                "'usr','','','',''," +
                "'pw','','','','')");
        stat.execute("INSERT INTO Jobs VALUES ('UID002','BR002','06/09/2010','05/30/2010'," +
                "2000.67,'success','active'," +
                "'usr','2 Jan Smuts Drive','','',''," +
                "'pw','','','','')");
        stat.execute("INSERT INTO Jobs VALUES ('UID002','BR003','06/09/2010','05/30/2010'," +
                "2000.67,'success','active'," +
                "'usr','9998765','','',''," +
                "'pw','1234','','','')");

        stat.execute("INSERT INTO Jobs VALUES ('UID003','BR001','06/09/2010','05/30/2010'," +
                "2000.67,'success','active'," +
                "'usr','','','',''," +
                "'pw','','','','')");
        stat.execute("INSERT INTO Jobs VALUES ('UID003','BR002','06/09/2010','05/30/2010'," +
                "2000.67,'success','inactive'," +
                "'usr','77 Jan Smuts Drive','','',''," +
                "'pw','','','','')");
        stat.execute("INSERT INTO Jobs VALUES ('UID003','BR003','06/09/2010','05/30/2010'," +
                "2000.67,'success','active'," +
                "'usr','9998765','','',''," +
                "'pw','1234','','','')");

        //* Common
        stat.execute("INSERT INTO Common VALUES ('UID001', 'BR001','acc190823'," +
                 " 'Peter Parker', '05/30/2010', 'no100922','May', 2000.67, " +
                 "'08/30/2010',10000.67, 2000.67, 8000,1000, 1000, 2000, 250)");

        stat.execute("INSERT INTO Common VALUES ('UID002', 'BR001','acc190823'," +
                 " 'David Donald', '05/30/2010', 'no100922','May', 2000.67, " +
                 "'08/30/2010',10000.67, 2000.67, 8000,1000, 1000, 2000, 250)");

        stat.execute("INSERT INTO Common VALUES ('UID002', 'BR002','acc190823'," +
                 " 'David Donald', '05/30/2010', 'no100922','May', 2000.67, " +
                 "'08/30/2010',10000.67, 2000.67, 8000,1000, 1000, 2000, 250)");

        stat.execute("INSERT INTO Common VALUES ('UID002', 'BR003','acc190823'," +
                 " 'Fred Frank', '05/30/2010', 'no100922','May', 2000.67, " +
                 "'08/30/2010',10000.67, 2000.67, 8000,1000, 1000, 2000, 250)");

        stat.execute("INSERT INTO Common VALUES ('UID003', 'BR001','acc190823'," +
                 " 'Fred Frank', '05/30/2010', 'no100922','May', 2000.67, " +
                 "'08/30/2010',10000.67, 2000.67, 8000,1000, 1000, 2000, 250)");

        stat.execute("INSERT INTO Common VALUES ('UID003', 'BR002','acc190823'," +
                 " 'Fred Frank', '05/30/2010', 'no100922','May', 2000.67, " +
                 "'08/30/2010',10000.67, 2000.67, 8000,1000, 1000, 2000, 250)");

         stat.execute("INSERT INTO Common VALUES ('UID003', 'BR003','acc190823'," +
                 " 'Fred Frank', '05/30/2010', 'no100922','May', 2000.67, " +
                 "'08/30/2010',10000.67, 2000.67, 8000,1000, 1000, 2000, 250)");

        //* Telco
         stat.execute("INSERT INTO Telco VALUES ('UID001', 'BR001','011 456 8870'" +
                 ", '13:45:56',55.55, 400.55, 88)");
         stat.execute("INSERT INTO Telco VALUES ('UID002', 'BR001','011 456 8870'" +
                 ", '13:45:56',55.55, 400.55, 88)");
         stat.execute("INSERT INTO Telco VALUES ('UID003', 'BR001','011 456 8870'" +
                 ", '13:45:56',55.55, 400.55, 88)");


        //* muncipality
         stat.execute("INSERT INTO Municipality VALUES('UID002', 'BR002','700KWh', " +
                "4002.88,'3456L', 30982.77,5523.00, 8832.77)");
        stat.execute("INSERT INTO Municipality VALUES('UID003', 'BR002','700KWh', " +
                "4002.88,'3456L', 30982.77,5523.00, 8832.77)");

        //*credit card
        stat.execute("INSERT INTO CreditCards VALUES('UID002', 'BR003','Visa'," +
                " 22.70,50000.00, 18000.70, 2000.00)");
        stat.execute("INSERT INTO CreditCards VALUES('UID003', 'BR003','Visa'," +
                " 22.70,50000.00, 18000.70, 2000.00)");

    }

    public void dropTables() throws SQLException
    {
        Statement stat = _Connection.createStatement();

        stat.execute("DROP TABLE Test");
        stat.execute("DROP TABLE BillingSites");
        stat.execute("DROP TABLE BillingSiteTypes");
        stat.execute("DROP TABLE Common");
        stat.execute("DROP TABLE CreditCards");
        stat.execute("DROP TABLE ErrorCodes");
        stat.execute("DROP TABLE Jobs");
        stat.execute("DROP TABLE Municipality");
        stat.execute("DROP TABLE Telco");
        stat.execute("DROP TABLE Users");

        //_Connection.close();
    }
}
